﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace SF.Base.Core {
	public class CommentRules {
		/// <summary>
		/// 查询某词汇表包含的全部心得
		/// </summary>
		/// <param name="taxonomyId">词汇表文本ID</param>
		/// <param name="userid">用户文本ID</param>
		/// <returns></returns>
		public static List<CommentEntity> GetDictCommentsByVocabularyId(string taxonomyId, string userId) {
			List<CommentEntity> lists = new List<CommentEntity>();

			string sql = @"
				select 
					b.cid,
					b.object_id,
					b.object_type,
					b.author,
					b.email,
					b.avatar,
					b.ip,
					b.comment_date,
					b.content,
					b.comment_type,
					b.approved,
					b.status,
					b.agent,
					b.user_id,
					b.parent,
					b.comment_id,
					--用户的数字ID
					uid = isnull((
						select 
							c.uid 
						from dt_users c 
						where b.user_id = b.user_id)
					,0),
					--父节点的数字ID
					pid = isnull((
						select 
							a.cid 
						from dt_comments a 
						where b.comment_id = b.parent )
					,0),
					spell = isnull((
						select 
							e.aspell 
						from dt_dicts e 
						where e.dict_id = d.taxonomy_id )
					,''), 
					did = isnull((
						select 
							f.did 
						from dt_dicts f 
						where f.dict_id = d.taxonomy_id )
					,0)
				 from dbo.dt_term_taxonomy d inner join dt_comments b  
				 on d.taxonomy_id = b.object_id 
					and b.object_type='dict' 
				 where d.taxonomy_id = @taxonomy_id 
					and b.status = 1 
					and (b.approved = 1 or b.user_id = @user_id) 
				 --过滤用户已屏蔽的评论
				 and b.comment_id not in ( 
					select 
						object_id 
					from dt_term_relationships 
					where taxonomy_id = (
						select 
							taxonomy_id 
						from dt_term_taxonomy 
						where user_id = @user_id 
							and taxonomy = 'screen-comment'
					)
				) order by b.cid desc
			 ";

			try {
				using (
					IDataReader dataReader = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						sql,
						Sh.GetParameter("@taxonomy_id", DbType.Guid, new Guid(taxonomyId)),
						Sh.GetParameter("@user_id", DbType.Guid, new Guid(userId))
					)
				) {
					while (dataReader.Read()) {

						CommentEntity item = BuildEntity.BuildCommentEntity(dataReader);
						item.Spell = Sh.GetStr(dataReader, "Spell");
						item.OID = Sh.GetInt(dataReader, "Did");
						item.ChildCount = Sh.GetInt(dataReader, "ChildCount");
						lists.Add(item);

					}
					Logger.Detail("DT_T_Comment", lists.Count.ToString());
				}
			}
			catch (System.Exception e) {
				Logger.Exception("查询词汇表包含的全部心得失败", e);
				throw new Exception("查询词汇表包含的全部心得失败", e);
			}

			return lists;
		}

		/// <summary>
		/// 根据评论数字ID获取评论对象
		/// </summary>
		/// <param name="cid">评论数字ID</param>
		/// <returns></returns>
		public static CommentEntity GetCommentByCid(int cid) {
			CommentEntity item = null;

			try {
				using (
					IDataReader dataReader = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						@"
							select 
								cid,
								object_id,
								object_type,
								author,
								email,
								avatar,
								ip,
								comment_date,
								content,
								comment_type,
								approved,
								status,
								agent,
								user_id,
								parent,
								comment_id, 
								uid = ISNULL((
									select 
										c.uid 
									from dt_users c 
									where c.user_id = b.user_id
								),0),  
								pid = isnull((
									select 
										a.cid
									from dt_comments a 
									where a.comment_id = b.parent
								),0) 
							from dt_comments b 
							where cid = @cid 
						",
						Sh.GetParameter("@cid", DbType.String, cid)
					)
				) {
					if (dataReader.Read()) {
						item = BuildEntity.BuildCommentEntity(dataReader);
					}
				}
			}
			catch (System.Exception e) {
				Logger.Exception("根据评论数字ID获取评论对象失败", e);
				throw new Exception("根据评论数字ID获取评论对象失败", e);
			}
			return item;
		}



		/// <summary>
		/// 根据评论父节点ID查询子评论列表
		/// </summary>
		/// <param name="parentid">评论父节点文本ID</param>
		/// <param name="userid">用户文本ID</param>
		/// <returns></returns>
		public static List<CommentEntity> GetChildCommentsByParentId(string parentId, string userId) {
			List<CommentEntity> lists = new List<CommentEntity>();

			string sql = @"
				 select 
					cid,
					object_id,
					object_type,
					author,
					email,
					avatar,
					ip,
					comment_date,
					content,
					comment_type,
					approved,
					status,
					agent,
					user_id,
					parent,
					comment_id, 
					--用户数字ID
					uid = ISNULL((
						select 
							c.uid 
						from dt_users c 
						where c.user_id = b.user_id
					),0),  
					--父节点数字ID
					pid = isnull((
						select 
							a.cid
						from dt_comments a 
						where a.comment_id = b.parent
					),0) 
				from dt_comments b 
				where parent = @parent 
					and status = 1
					 --过滤用户已屏蔽的评论
					 and b.comment_id not in ( 
						select 
							object_id 
						from dt_term_relationships 
						where taxonomy_id = (
							select 
								taxonomy_id 
							from dt_term_taxonomy 
							where user_id = @user_id 
								and taxonomy = 'screen-comment'
						)
					)
			";
			try {
				using (
					IDataReader dataReader = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						sql,
						Sh.GetParameter("@parent", DbType.Guid, new Guid(parentId)),
						Sh.GetParameter("@user_id", DbType.Guid, new Guid(userId))
					)
				) {
					while (dataReader.Read()) {
						CommentEntity item = BuildEntity.BuildCommentEntity(dataReader);
						lists.Add(item);

					}
				}
			}
			catch (System.Exception e) {
				Logger.Exception("根据评论父节点ID查询子评论列表失败", e);
				throw new Exception("根据评论父节点ID查询子评论列表失败", e);
			}
			return lists;
		}

		/// <summary>
		/// 根据评论父节点ID查询用户自己的子评论列表
		/// </summary>
		/// <param name="parentid">评论父节点文本ID</param>
		/// <param name="userid">用户文本ID</param>
		/// <returns></returns>
		public static List<CommentEntity> GetUserChildCommentsByParentId(string parentId, string userId) {
			List<CommentEntity> lists = new List<CommentEntity>();

			string sql = @"
				 select 
					cid,
					object_id,
					object_type,
					author,
					email,
					avatar,
					ip,
					comment_date,
					content,
					comment_type,
					approved,
					status,
					agent,
					user_id,
					parent,
					comment_id, 
					--用户数字ID
					uid = ISNULL((
						select 
							c.uid 
						from dt_users c 
						where c.user_id = b.user_id
					),0),  
					--父节点数字ID
					pid = isnull((
						select 
							a.cid
						from dt_comments a 
						where a.comment_id = b.parent
					),0) 
				from dt_comments b 
				where parent = @parent 
					and status = 1
					and user_id = @user_id
			";
			try {
				using (
					IDataReader dataReader = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						sql,
						Sh.GetParameter("@parent", DbType.Guid, new Guid(parentId)),
						Sh.GetParameter("@user_id", DbType.Guid, new Guid(userId))
					)
				) {
					while (dataReader.Read()) {
						CommentEntity item = BuildEntity.BuildCommentEntity(dataReader);
						lists.Add(item);

					}
				}
			}
			catch (System.Exception e) {
				Logger.Exception("根据评论父节点ID查询子评论列表失败", e);
				throw new Exception("根据评论父节点ID查询子评论列表失败", e);
			}
			return lists;
		}

		/// <summary>
		/// 查询对象的第一层级评论列表(包括已被用户屏蔽的心得或评论)
		/// </summary>
		/// <param name="ancestorid">祖先文本ID【DictId】</param>
		/// <param name="commenttype">评论类型</param>
		/// <returns></returns>
		public static List<CommentEntity> GetFirstLevelCommentsByObjectId(string objectId) {
			List<CommentEntity> lists = new List<CommentEntity>();

			string sql = @"
				 select 
					cid,
					object_id,
					object_type,
					author,
					email,
					avatar,
					ip,
					comment_date,
					content,
					comment_type,
					approved,
					status,
					agent,
					user_id,
					parent,
					comment_id, 
					--用户数字ID
					uid = isnull((
						select 
							c.uid 
						from dt_users c 
						where c.user_id = b.user_id
					),0),  
					--父节点数字ID
					pid = isnull((
						select 
							a.cid
						from dt_comments a 
						where a.comment_id = b.parent
					),0) 
				from dt_comments b 
				where object_id = @object_id
					and parent = '00000000-0000-0000-0000-000000000000'
					and status = 1
				order by cid desc
			";

			Logger.Detail(sql);

			try {
				using (
					IDataReader dataReader = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						sql,
						Sh.GetParameter("@object_id", DbType.String, objectId)
					)
				) {
					while (dataReader.Read()) {
						CommentEntity item = BuildEntity.BuildCommentEntity(dataReader);
						lists.Add(item);

					}
				}
			}
			catch (System.Exception e) {
				Logger.Exception("查询对象的第一层级评论列表失败", e);
				throw new Exception("查询对象的第一层级评论列表失败", e);
			}
			return lists;
		}

		/// <summary>
		/// 查询对象的第一层级评论列表（排除已被用户屏蔽的评论）
		/// </summary>
		/// <param name="ancestorid">祖先文本ID【DictId】</param>
		/// <param name="ancestortype">评论类型</param>
		/// <param name="userid">用户文本ID</param>
		/// <param name="top">取前N条</param>
		/// <returns>评论列表</returns>
		public static List<CommentEntity> GetFirstLevelCommentsByObjectId(string objectId, string commenttype, string userId, int top) {
			return GetFirstLevelCommentsByObjectId(objectId, commenttype, userId, "top 3");
		}

		public static List<CommentEntity> GetFirstLevelCommentsByObjectId(string objectId, string commenttype, string userId) {
			return GetFirstLevelCommentsByObjectId(objectId, commenttype, userId, "");
		}

		/// <summary>
		/// 查询对象的第一层级评论列表（排除已被用户屏蔽的评论）
		/// </summary>
		/// <param name="ancestorid">祖先文本ID【DictId】</param>
		/// <param name="commenttype">评论类型【comment】</param>
		/// <param name="userid">用户文本ID</param>
		/// <returns></returns>
		public static List<CommentEntity> GetFirstLevelCommentsByObjectId(string objectId, string commentType, string userId, string top) {
			List<CommentEntity> lists = new List<CommentEntity>();

			string sql = @"
				 select " + top + @"
					cid,
					object_id,
					object_type,
					author,
					email,
					avatar,
					ip,
					comment_date,
					content,
					comment_type,
					approved,
					status,
					agent,
					user_id,
					parent,
					comment_id, 
					--用户数字ID
					uid = isnull((
						select 
							c.uid 
						from dt_users c 
						where c.user_id = b.user_id
					),0),  
					--父节点数字ID
					pid = isnull((
						select 
							a.cid
						from dt_comments a 
						where a.comment_id = b.parent
					),0) ,
					childcount = isnull((
						select 
							count(*) 
						from dt_comments d 
						where d.parent = b.comment_id 
							and d.Status = 1 and 
							(d.Approved = 1 or d.user_id = @user_id) 
							--过滤用户已屏蔽的评论
							and d.comment_id not in ( 
								select 
									object_id 
								from dt_term_relationships 
								where taxonomy_id = (
									select 
										taxonomy_id 
									from dt_term_taxonomy 
									where user_id = @user_id 
										and taxonomy = 'screen-comment'
								)
							)
					),0)
				from dt_comments b 
				where object_id = @object_id
					and status = 1 
					and parent = '00000000-0000-0000-0000-000000000000'
					and (approved = 1 or user_id = @user_id)
					--过滤用户已屏蔽的评论
					and b.comment_id not in ( 
						select 
							object_id 
						from dt_term_relationships 
						where taxonomy_id = (
							select 
								taxonomy_id 
							from dt_term_taxonomy 
							where user_id = @user_id 
								and taxonomy = 'screen-comment'
						)
					)
					order by cid desc
			";

			Logger.Detail(sql);

			try {
				using (
					IDataReader dataReader = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						sql,
						Sh.GetParameter("@object_id", DbType.String, objectId),
						Sh.GetParameter("@user_id", DbType.String, userId),
						Sh.GetParameter("@comment_type", DbType.String, commentType)
					)
				) {
					while (dataReader.Read()) {
						CommentEntity item = BuildEntity.BuildCommentEntity(dataReader);
						item.ChildCount = Sh.GetInt(dataReader, "ChildCount");
						lists.Add(item);
					}
					Logger.Detail("DT_T_Comment", lists.Count.ToString());
				}
			}
			catch (System.Exception e) {
				Logger.Exception("查询[单个单词]的评论列表失败", e);
				throw new Exception("查询[单个单词]的评论列表失败", e);
			}
			return lists;
		}

		/// <summary>
		/// 查询对象的第一层级评论数量
		/// </summary>
		/// <param name="ancestorid">祖先文本ID【DictId】</param>
		/// <param name="commenttype">评论类型</param>
		/// <param name="userid">用户文本ID</param>
		/// <returns></returns>
		public static int GetFirstLevelCommentsCountByObjectId(string objectId, string commenttype, string userId) {
			List<CommentEntity> lists = new List<CommentEntity>();

			string sql = @"
				select 
					count(*)
				from dt_comments b 
				where object_id = @object_id
					and status = 1 
					and parent = '00000000-0000-0000-0000-000000000000'
					and (approved = 1 or user_id = @user_id)
					--过滤用户已屏蔽的评论
					and b.comment_id not in ( 
						select 
							object_id 
						from dt_term_relationships 
						where taxonomy_id = (
							select 
								taxonomy_id 
							from dt_term_taxonomy 
							where user_id = @user_id 
								and taxonomy = 'screen-comment'
						)
					)
			";

			Logger.Detail(objectId, commenttype, userId, sql);

			try {
				object o = Sh.ExecuteScalar(
					BaseSettings.Instance.ConnSF,
					CommandType.Text,
					sql,
					Sh.GetParameter("@object_id", DbType.String, objectId),
					Sh.GetParameter("@user_id", DbType.String, userId)
				);
				if (o == null) {
					return -160308;
				}
				return int.Parse(o.ToString());
			}
			catch (System.Exception e) {
				Logger.Exception("查询对象的第一层级评论数量失败", e);
				return -160308;
			}
		}

		/// <summary>
		/// 发布评论
		/// </summary>
		/// <param name="comment">评论对象</param>
		/// <returns>返回评论ID</returns>
		public static int Insert(CommentEntity comment) {
			string PostContent = string.Empty;
			string PostObjectType = ObjectType.Comment;
			if (comment.ObjectType == ObjectType.Dict) {
				DictEntity Dict = DictRules.GetDictByDictIdFromCache(comment.ObjectId);

				PostContent = "<div class='title'><span class='time'>" + DateTime.Now.ToString("HH:mm") + "</span>评论<a href='/dict.aspx?i=" + Dict.Did + "'><span class='spell'>" + Dict.ASpell + "</span></a><span class='pron'>" + Dict.APronunciation.Replace("<SUP>", "(").Replace("</SUP>", ")").Replace("|", "<img border='0' src='/Common/images/compart.png' class='compart'/>") + "</span></div>";
				PostContent += "<div class='content'>" + comment.Content + "</div>";
			}

			string sql = @" 
				insert into dt_posts (
					content,post_date,object_id,object_type,user_id
				)
				values(
					@post_content,getdate(),@comment_id,@post_object_type,@user_id
				)
				insert into dt_comments (
					object_id,object_type,author,email,avatar,ip,comment_date,
					content,comment_type,approved,status,agent,user_id,parent,comment_id
				) 
				values (
					@object_id,@object_type,@author,@email,@avatar,@ip,@comment_date,
					@content,@comment_type,@approved,@status,@agent,@user_id,@parent,@comment_id
				); 
				select @@IDENTITY ";

			IDataParameter[] ids = {
				Sh.GetParameter("@object_id",DbType.String,comment.ObjectId),
                Sh.GetParameter("@object_type",DbType.String,comment.ObjectType),
				Sh.GetParameter("@author",DbType.String,comment.Author),
                Sh.GetParameter("@email",DbType.String,comment.Email),
                Sh.GetParameter("@avatar",DbType.String,comment.Avatar),
                Sh.GetParameter("@ip",DbType.String,comment.IP),
                Sh.GetParameter("@comment_date",DbType.DateTime,comment.CommentDate),
                Sh.GetParameter("@content",DbType.String,comment.Content),
				Sh.GetParameter("@comment_type",DbType.String,comment.CommentType),
                Sh.GetParameter("@approved",DbType.Int32,comment.Approved),
				Sh.GetParameter("@status",DbType.Int32,comment.Status),
                Sh.GetParameter("@agent",DbType.String,comment.Agent),
                
                Sh.GetParameter("@user_id",DbType.Guid,new Guid(comment.UserId)),
                Sh.GetParameter("@parent",DbType.Guid,new Guid(comment.ParentId)),
				Sh.GetParameter("@comment_id",DbType.Guid,new Guid(comment.CommentId)),

				Sh.GetParameter("@post_object_type",DbType.String,PostObjectType),
				Sh.GetParameter("@post_content",DbType.String,PostContent)
            };

			using (SqlConnection conn = new SqlConnection(BaseSettings.Instance.ConnSF)) {
				conn.Open();
				SqlTransaction trans = conn.BeginTransaction();

				try {
					object ret = Sh.ExecuteScalar(
						trans,
						CommandType.Text,
						sql, 
						ids
					);

					if (ret == null) {
						trans.Rollback();
						return -160301;
					}
					trans.Commit();
					return int.Parse(ret.ToString());
				}
				catch (System.Exception e) {
					Logger.Exception("发布评论失败", e);
					trans.Rollback();
					return -160301;
				}
			}
		}

		/// <summary>
		/// 查询用户屏蔽的评论列表
		/// </summary>
		/// <param name="userId">用户ID</param>
		/// <returns></returns>
		public static List<CommentEntity> GetUserScreenComments(string userId) {
			List<CommentEntity> lists = new List<CommentEntity>();
			string sql = @"
						select 
							cid,
							object_id,
							object_type,
							author,
							email,
							avatar,
							ip,
							comment_date,
							content,
							comment_type,
							approved,
							status,
							agent,
							user_id,
							parent,
							comment_id, 
							--用户数字ID
							uid = isnull((
								select 
									c.uid 
								from dt_users c 
								where c.user_id = b.user_id
							),0),  
							--父节点数字ID
							pid = isnull((
								select 
									a.cid
								from dt_comments a 
								where a.comment_id = b.parent
							),0) 
						from dt_comments 
						where comment_id in(
							select 
								object_id 
							from dt_term_relationships
							where taxonomy_id = (
								select 
									taxonomy_id 
								from dt_term_taxonomy
								where user_id = @user_id
									and taxonomy = 'screen-comment'
						)
					";
			Logger.Detail(userId, sql);
			try {
				using (
					IDataReader dataReader = Sh.ExecuteReader(
						BaseSettings.Instance.ConnSF,
						CommandType.Text,
						sql,
						Sh.GetParameter("@user_id", DbType.String, userId)
					)
				) {
					while (dataReader.Read()) {
						CommentEntity item = BuildEntity.BuildCommentEntity(dataReader);
						item.ChildCount = Sh.GetInt(dataReader, "ChildCount");
						lists.Add(item);
					}
					Logger.Detail("GetUserScreenComments", lists.Count.ToString());
				}
			}
			catch (System.Exception e) {
				Logger.Exception("查询用户屏蔽的评论列表", e);
				throw new Exception("查询用户屏蔽的评论列表失败", e);
			}
			return lists;
		}

		/// <summary>
		/// 用户屏蔽评论
		/// </summary>
		/// <param name="cid">评论的数字ID</param>
		/// <param name="userid">用户的文本ID</param>
		/// <returns></returns>
		public static int Screen(int cid, string userId) {
			try {
				int ret = Sh.ExecuteNonQuery(
					BaseSettings.Instance.ConnSF,
					CommandType.Text,
					@" 
						if exists ( select 1 from dt_term_taxonomy where user_id = @user_id and taxonomy = 'screen-comment')
						begin
							insert into dt_term_relationships ([object_id],taxonomy_id,relation_order,relation_date) 
							select 
								comment_id = isnull((select comment_id from dt_comments where cid = @cid),0),
								taxonomy_id,
								0,
								getdate()
							from dt_term_taxonomy
							where  user_id = @user_id 
								and taxonomy = 'screen-comment'
						end
						else 
						begin
							declare @guid uniqueidentifier
							set @guid = newid()
							insert into dbo.dt_term_taxonomy (taxonomy,[name],[slug],[description],[user_id],parent,taxonomy_order,[status])
							values('screen-comment','屏蔽评论',null,'屏蔽评论',@guid,null,0,1)

							insert into dt_term_relationships ([object_id],taxonomy_id,relation_order,relation_date) 
							select 
								comment_id,
								@guid,
								0,
								getdate()
							from dt_comments
							where cid = @cid
						end
					",
					Sh.GetParameter("@user_id", DbType.String, userId),
					Sh.GetParameter("@cid", DbType.String, cid)
				);
				if (ret == 1) {
					return 0;
				}
				else {
					return -160303;
				}
			}
			catch (System.Exception e) {
				Logger.Exception("屏蔽评论失败", e);
				return -160303;
			}
		}

		/// <summary>
		/// 用户删除评论
		/// </summary>
		/// <param name="cid">评论的数字ID</param>
		/// <param name="userid">用户的文本ID</param>
		/// <returns></returns>
		public static int Delete(int cid, string userId) {
			try {
				int ret = Sh.ExecuteNonQuery(
					BaseSettings.Instance.ConnSF,
					CommandType.Text,
					" update dt_comments set status = 0 where user_id = @user_id and cid =@cid and status = 1 ",
					Sh.GetParameter("@user_id", DbType.String, userId),
					Sh.GetParameter("@cid", DbType.Int32, cid)
				);
			}
			catch (System.Exception e) {
				Logger.Exception("删除评论失败", e);
				return -160302;
			}

			return 0;
		}

	}
}
